import pandas as pd
# 尝试读取上传的Excel文件
from datetime import datetime
import os
# 保存内容到文件
import subprocess
import shutil
def excel_hearder(df):
    # 读取Excel文件中的所有工作表
    try:
        # 加载Excel文件
            # 获取表头
            # 读取前几行来尝试确定表头的行数
            # 找出连续的非空行作为表头
            # 使用确定的表头行数重新读取Excel文件
        df = df.fillna('""')
        multi_header_data = df.head(2).values.tolist() if not df.empty else []
          # 获取前三行数据作为表头
        # 转换DataFrame为字符串，以便进行文本片段的切分
        # df_str = df.to_string(index=False)
        df = df.applymap(lambda x: str(x).replace('\n', ''))

        df_str = '\n'.join('|'.join(map(str, row)) for row in df.values)

    except Exception as e:
        multi_header_data, df_str = e, None


    return multi_header_data,df_str
  # 显示表头和内容的前1000个字符，避免输出过长信息

# 定义一个函数来切分文本，确保每行数据不分开
def split_text_into_chunks(text, chunk_size=3000):
    chunks = []
    current_chunk = ""
    for line in text.splitlines():
        # 如果添加新行后长度超过chunk_size，则保存当前chunk并开始新的chunk
        if len(current_chunk) + len(line) + 1 > chunk_size:
            chunks.append(current_chunk)
            current_chunk = str([line]) + "\n"  # 开始新chunk，并添加换行符
        else:
            current_chunk += str([line]) + "\n"  # 添加当前行到chunk，并添加换行符
    # 添加最后一个chunk（如果有剩余）
    if current_chunk:
        chunks.append(current_chunk)
    return chunks
def excel_process(file_path,filename,output_directory):
    #current_time = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")

    # 加载Excel文件
    all_sheets = pd.read_excel(file_path, sheet_name=None,header=None,na_values=[])
    for sheet_name, df in all_sheets.items():
        fil_path = f'{output_directory}/{filename}_{sheet_name}.txt'  # +'_'+current_time+".txt"
        file = open(fil_path, 'w', encoding='utf-8')
        multi_header_data,df_str=excel_hearder(df)
        multi_header_data=[str(i) for i in multi_header_data]
        print('sheet_name:',sheet_name)
        print('表头为\n',multi_header_data)

        chunks = split_text_into_chunks(df_str)
        print('filename',filename)
        chunks_ = [str(''.join(multi_header_data)).lower().replace('nan','') +'\n'+ str(c).replace('nan','').replace('NaN','') if i != 0 else c for i, c in enumerate(chunks)]
        for i in chunks_:
            print(len(str(i)))
            file.writelines(str(filename)+'\n'+i+'\n\n')
        print(f'文件保存在：{fil_path}')
    # 获取当前时间并格式化为字符串


def main():
    # 设置源目录和输出目录
    source_directory = './DataPreprocessing'
    output_directory = './DataGeneration'

    # 确保输出目录存在
    if not os.path.exists(output_directory):
        os.makedirs(output_directory)

    # 遍历源目录
    for root, dirs, files in os.walk(source_directory):
        for file in files:
            # 构建源文件和目标文件的完整路径
            source_file = os.path.join(root, file)
            # 创建目标目录的相对路径

            target_file = os.path.join(output_directory, file)
            filename, _ = os.path.splitext(file)
            # 检查文件扩展名是否为 .doc
            if file.lower().endswith(('.xls', '.xlsx')):
                excel_process(source_file, filename, output_directory)


if __name__=="__main__":
    main()